package com.jourwon.spring.boot.util;

import lombok.AllArgsConstructor;
import lombok.Getter;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.util.Arrays;
import java.util.List;
import java.util.Objects;

/**
 * @author JourWon
 * @date 2023/4/24
 */
public class SqlBuilder {

    private static final String SELECT = "SELECT";
    private static final String INSERT_INTO = "INSERT INTO";
    private static final String VALUES = "VALUES";
    private static final String FROM = "FROM";
    private static final String ASTERISK = "*";
    private static final String BLANK = " ";
    private static final String COMMA = ",";
    private static final String QUOTATION = "'";
    private static final String LEFT_PARENTHESES = "(";
    private static final String RIGHT_PARENTHESES = ")";
    private static final String COMMA_BLANK = ", ";

    private static final String WHERE = "WHERE";

    private static final String BETWEEN = "BETWEEN";
    private static final String AND = "AND";
    private static final String OR = "OR";
    private static final String LIKE_CONCAT = "LIKE CONCAT";

    private static final String LIKE_START_WITH = ", '%') ";

    private static final String LIKE_END_WITH = "('%', ";

    private static final String ORDER_BY = "ORDER BY";
    private static final String LIMIT = "LIMIT";

    private static final String OFFSET = "OFFSET";

    private StringBuilder sql;

    public SqlBuilder() {
    }

    @Override
    public String toString() {
        return this.sql.toString();
    }

    public SqlBuilder(SelectBuilder selectBuilder) {
        this.sql = selectBuilder.sql;
    }

    public static SelectBuilder selectBuilder() {
        return new SelectBuilder();
    }

    public SqlBuilder(InsertBuilder insertBuilder) {
        this.sql = insertBuilder.sql;
    }

    public static InsertBuilder insertBuilder() {
        return new InsertBuilder();
    }

    public SqlBuilder(UpdateBuilder updateBuilder) {
        this.sql = updateBuilder.sql;
    }

    public static UpdateBuilder updateBuilder() {
        return new UpdateBuilder();
    }

    public SqlBuilder(DeleteBuilder deleteBuilder) {
        this.sql = deleteBuilder.sql;
    }

    public static DeleteBuilder deleteBuilder() {
        return new DeleteBuilder();
    }

    @Getter
    public static class SelectBuilder {

        private StringBuilder sql = new StringBuilder(1024);

        public SelectBuilder() {
        }

        public SelectBuilder(StringBuilder sql) {
            this.sql = sql;
        }

        public SqlBuilder build() {
            return new SqlBuilder(this);
        }

        /**
         * select到表名的sql拼接(查询所有字段)
         *
         * @param tableName 表名称
         * @return MySqlBuilder
         */
        public SelectBuilder selectFrom(String tableName) {
            this.sql.append(SELECT).append(BLANK).append(ASTERISK).append(BLANK).append(FROM).append(BLANK).append(tableName).append(BLANK);

            return this;
        }

        /**
         * select到表名的sql拼接
         *
         * @param tableName 表名称
         * @param fields    字段集合
         * @return MySqlBuilder
         */
        public SelectBuilder selectFrom(String tableName, Iterable<? extends CharSequence> fields) {
            this.sql.append(SELECT).append(BLANK).append(String.join(COMMA_BLANK, fields)).append(BLANK).append(FROM).append(BLANK).append(tableName).append(BLANK);

            return this;
        }

    }

    @Getter
    public static class InsertBuilder {

        private StringBuilder sql = new StringBuilder(1024);

        public InsertBuilder() {
        }

        public InsertBuilder(StringBuilder sql) {
            this.sql = sql;
        }

        public SqlBuilder build() {
            return new SqlBuilder(this);
        }

        /**
         * select到表名的sql拼接(查询所有字段)
         *
         * @param tableName 表名称
         * @return MySqlBuilder
         */
        public InsertBuilder insertInto(String tableName) {
            this.sql.append(INSERT_INTO)
                    .append(BLANK).append(tableName)
                    .append(BLANK).append(VALUES).append(BLANK);

            return this;
        }

        /**
         * select到表名的sql拼接
         *
         * @param tableName 表名称
         * @param fields    字段集合
         * @return MySqlBuilder
         */
        public InsertBuilder insertInto(String tableName, Iterable<? extends CharSequence> fields) {
            this.sql.append(INSERT_INTO)
                    .append(BLANK).append(tableName).append(BLANK)
                    .append(LEFT_PARENTHESES)
                    .append(String.join(COMMA_BLANK, fields))
                    .append(RIGHT_PARENTHESES)
                    .append(BLANK).append(VALUES).append(BLANK);

            return this;
        }

    }

    @Getter
    public static class UpdateBuilder {

        private StringBuilder sql = new StringBuilder(1024);

        public UpdateBuilder() {
        }

        public UpdateBuilder(StringBuilder sql) {
            this.sql = sql;
        }

        public SqlBuilder build() {
            return new SqlBuilder(this);
        }

    }

    @Getter
    public static class DeleteBuilder {

        private StringBuilder sql = new StringBuilder(1024);

        public DeleteBuilder() {
        }

        public DeleteBuilder(StringBuilder sql) {
            this.sql = sql;
        }

        public SqlBuilder build() {
            return new SqlBuilder(this);
        }

    }

    /**
     * where
     *
     * @return MySqlBuilder
     */
    public SqlBuilder where() {
        this.sql.append(WHERE).append(BLANK);
        return this;
    }

    /**
     * and
     *
     * @return MySqlBuilder
     */
    public SqlBuilder and() {
        this.sql.append(AND).append(BLANK);
        return this;
    }

    /**
     * or
     *
     * @return MySqlBuilder
     */
    public SqlBuilder or() {
        this.sql.append(OR).append(BLANK);
        return this;
    }

    /**
     * 小于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder less(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.LESS);
    }

    /**
     * 小于等于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder lessEq(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.LESS_EQ);
    }

    /**
     * 大于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder great(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.GREAT);
    }

    /**
     * 大于等于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder greatEq(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.GREAT_EQ);
    }

    /**
     * 等于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder equal(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.EQUAL);
    }

    /**
     * 不等于
     *
     * @param field 字段
     * @param value 值
     * @return MySqlBuilder
     */
    public SqlBuilder notEqual(String field, Object value) {
        return valueOperator(field, value, OperatorEnum.NOT_EQUAL);
    }

    /**
     * between
     *
     * @param field 字段
     * @param left  最小值
     * @param right 最大值
     * @return MySqlBuilder
     */
    public SqlBuilder between(String field, Object left, Object right) {
        if (isNotBlank(left) && isNotBlank(right)) {
            this.sql.append(field).append(BLANK).append(BETWEEN).append(BLANK)
                    .append(this.wrapperSqlValue(left)).append(BLANK).append(AND).append(BLANK).append(this.wrapperSqlValue(right)).append(BLANK);
        }

        return this;
    }

    /**
     * in
     *
     * @param field  字段
     * @param values 值
     * @param <T>    泛型
     * @return MySqlBuilder
     */
    public <T> SqlBuilder in(String field, List<T> values) {
        return listOperator(field, values, OperatorEnum.IN);
    }

    /**
     * not in
     *
     * @param field  字段
     * @param values 值
     * @param <T>    泛型
     * @return MySqlBuilder
     */
    public <T> SqlBuilder notIn(String field, List<T> values) {
        return listOperator(field, values, OperatorEnum.NOT_IN);
    }

    /**
     * 模糊查询
     *
     * @param field    字段
     * @param value    值
     * @param likeEnum 模糊类别
     * @return MySqlBuilder
     */
    public SqlBuilder like(String field, Object value, LikeEnum likeEnum) {
        if (isBlank(value)) {
            return this;
        }

        this.sql.append(field).append(BLANK).append(LIKE_CONCAT).append(BLANK);
        if (value instanceof String) {
            switch (likeEnum) {
                case START_WITH:
                    this.sql.append(LEFT_PARENTHESES).append(QUOTATION).append(value).append(QUOTATION).append(LIKE_START_WITH);
                    break;
                case END_WITH:
                    this.sql.append(LIKE_END_WITH).append(QUOTATION).append(value).append(QUOTATION).append(RIGHT_PARENTHESES).append(BLANK);
                    break;
                case CONTAINS:
                    this.sql.append(LIKE_END_WITH).append(QUOTATION).append(value).append(QUOTATION).append(LIKE_START_WITH);
                    break;

                default:
                    break;
            }
        } else {
            switch (likeEnum) {
                case START_WITH:
                    this.sql.append(LEFT_PARENTHESES).append(value).append(LIKE_START_WITH);
                    break;
                case END_WITH:
                    this.sql.append(LIKE_END_WITH).append(value).append(RIGHT_PARENTHESES).append(BLANK);
                    break;
                case CONTAINS:
                    this.sql.append(LIKE_END_WITH).append(value).append(LIKE_START_WITH);
                    break;

                default:
                    break;
            }
        }

        return this;
    }

    /**
     * orderBy
     *
     * @param sortField 字段
     * @param sortOrderEnum 顺序
     * @return MySqlBuilder
     */
    public SqlBuilder orderBy(String sortField, SortOrderEnum sortOrderEnum) {
        if (StringUtils.isNotBlank(sortField)) {
            this.sql.append(ORDER_BY).append(BLANK)
                    .append(sortField)
                    .append(BLANK)
                    .append(sortOrderEnum.getValue())
                    .append(BLANK);
        }

        return this;
    }

    /**
     * limit
     *
     * @param value 查询结果数
     * @return MySqlBuilder
     */
    public SqlBuilder limit(Integer value) {
        if (Objects.nonNull(value)) {
            this.sql.append(LIMIT).append(BLANK).append(value).append(BLANK);
        }

        return this;
    }

    /**
     * page
     *
     * @param pageNum  页码
     * @param pageSize 页大小
     * @return MySqlBuilder
     */
    public SqlBuilder page(Integer pageNum, Integer pageSize) {
        if (Objects.nonNull(pageNum) && Objects.nonNull(pageSize)) {
            this.sql.append(LIMIT).append(BLANK)
                    .append(pageSize)
                    .append(BLANK).append(OFFSET).append(BLANK)
                    .append((pageNum - 1) * pageSize)
                    .append(BLANK);
        }

        return this;
    }

    private <T> SqlBuilder listOperator(String field, List<T> values, OperatorEnum operatorEnum) {
        if (!CollectionUtils.isEmpty(values)) {
            if (OperatorEnum.IN.equals(operatorEnum) || OperatorEnum.NOT_IN.equals(operatorEnum)) {
                this.sql.append(field).append(BLANK).append(operatorEnum.getValue()).append(BLANK).append(LEFT_PARENTHESES);

                int size = values.size();

                for (int index = 0; index < size; index++) {
                    this.sql.append(this.wrapperSqlValue(values.get(index)));

                    if (index == (size - 1)) {
                        this.sql.append(RIGHT_PARENTHESES).append(BLANK);
                        break;
                    }

                    this.sql.append(COMMA);
                }
            }
        }

        return this;
    }

    private SqlBuilder valueOperator(String field, Object value, OperatorEnum operatorEnum) {
        if (Objects.nonNull(value)) {
            this.sql.append(field)
                    .append(BLANK).append(operatorEnum.getValue()).append(BLANK)
                    .append(this.wrapperSqlValue(value)).append(BLANK);
        }

        return this;
    }

    private static boolean isNotBlank(Object value) {
        return Objects.nonNull(value) && StringUtils.isNotBlank(value.toString());
    }

    private static boolean isBlank(Object value) {
        return Objects.isNull(value) || StringUtils.isBlank(value.toString());
    }

    private static String wrapperSqlValue(Object value) {
        if (value instanceof String) {
            return QUOTATION + value + QUOTATION;
        } else {
            return value.toString();
        }
    }

    public SqlBuilder append(String str) {
        if (StringUtils.isNotBlank(str)) {
            this.sql.append(str).append(BLANK);
        }

        return this;
    }

    @Getter
    @AllArgsConstructor
    public enum OperatorEnum {

        EQUAL("="),
        NOT_EQUAL("!="),
        GREAT(">"),
        GREAT_EQ(">="),
        LESS("<"),
        LESS_EQ("<="),
        IN("IN"),
        NOT_IN("NOT IN");

        private final String value;
    }

    /**
     * 排序方式（升序或者降序）
     */
    @Getter
    @AllArgsConstructor
    public enum SortOrderEnum {

        /**
         * 升序
         */
        ASC("ASC"),

        /**
         * 降序
         */
        DESC("DESC");

        private final String value;
    }

    @Getter
    @AllArgsConstructor
    public enum LikeEnum {
        /**
         * 以给定值开头，拼接后的SQL "value%"
         */
        START_WITH,
        /**
         * 以给定值开头，拼接后的SQL "%value"
         */
        END_WITH,
        /**
         * 包含给定值，拼接后的SQL "%value%"
         */
        CONTAINS
    }

}
